﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using CustomsSystem.Model;
using Maticsoft.DBUtility;

namespace CustomsSystem.DAL
{
    public class DocumentService
    {
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string docID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from T_document");
            strSql.Append(" where" + docID + " ");
            return DbHelperSQL.Exists(strSql.ToString());
        }
        /// <summary>
        /// 导出签收表excel
        /// </summary>
        /// <param name="strwhere"></param>
        /// <returns></returns>
        public DataSet GetView(string strwhere)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append(@"select a.sheetID,b.CustomerName '客户名',a.transferNo1 '编号',a.clearanceDate '日期',a.shipName '船名',a.goodsName '货名',a.No '提单数',a.ladingBillNo '提货单号','报关单号'=a.customsNo,isnull(sum(nums),0) 合计  ");
            strsql.Append(@" from T_sheetInfo a left join Cu_customer b on(a.cID=b.ID)  left join T_document e on (e.sheetid=a.sheetid )");
            if (strwhere != "")
            {
                strsql.Append(" where " + strwhere+" ");
            }
            strsql.Append(@"group by a.sheetID,b.CustomerName,a.transferNo1,a.clearanceDate,a.shipName,a.No,a.shipName,a.goodsName,a.ladingBillNo,a.customsNo");
            return DbHelperSQL.Query(strsql.ToString());
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(CustomsSystem.Model.document model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();

            if (model.sheetID != null)
            {
                strSql1.Append("sheetID,");
                strSql2.Append("'" + model.sheetID + "',");
            }
            if (model.docType != null)
            {
                strSql1.Append("docType,");
                strSql2.Append("'" + model.docType + "',");
            }
            if (model.docName1 != null)
            {
                strSql1.Append("docName1,");
                strSql2.Append("'" + model.docName1 + "',");
            }
            if (model.docName2 != null)
            {
                strSql1.Append("docName2,");
                strSql2.Append("'" + model.docName2 + "',");
            }

            if (model.docName3 != null)
            {
                strSql1.Append("docName3,");
                strSql2.Append("'" + model.docName3 + "',");
            }
            if (model.docName4 != null)
            {
                strSql1.Append("docName4,");
                strSql2.Append("'" + model.docName4 + "',");
            }
            if (model.docName5 != null)
            {
                strSql1.Append("docName5,");
                strSql2.Append("'" + model.docName5 + "',");
            }
            if (model.remark != null)
            {
                strSql1.Append("remark,");
                strSql2.Append("'" + model.remark + "',");
            }

            if (model.inputEmpNo != null)
            {
                strSql1.Append("inputEmpNo,");
                strSql2.Append("" + model.inputEmpNo + ",");
            }
            if (model.inputDate != null)
            {
                strSql1.Append("inputDate,");
                strSql2.Append("'" + model.inputDate + "',");
            }
            if (model.nums != null)
            {
                strSql1.Append("nums,");
                strSql2.Append("" + model.nums + ",");
            }

            strSql.Append("insert into T_document(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int docID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_document ");
            strSql.Append(" where docID=" + docID + "");
            int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CustomsSystem.Model.document GetModel(int sheetID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1  ");
            strSql.Append(" docID,sheetID,docType,docName1,docName2,docName3,docName4,docName5,remark,inputEmpNo,inputDate,nums ");
            strSql.Append(" from T_document ");
            strSql.Append(" where sheetID=" + sheetID + "");
            CustomsSystem.Model.document model = new CustomsSystem.Model.document();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        public CustomsSystem.Model.document DataRowToModel(DataRow row)
        {
            CustomsSystem.Model.document model = new CustomsSystem.Model.document();
            if (row != null)
            {
                if (row["sheetID"] != null && row["sheetID"].ToString() != "")
                {
                    model.sheetID = int.Parse(row["sheetID"].ToString());
                }
                if (row["docType"] != null && row["docType"].ToString() != "")
                {
                    model.docType = row["docType"].ToString();
                }
                if (row["docName1"] != null && row["docName1"].ToString() != "")
                {
                    model.docName1 = row["docName1"].ToString();
                }
                if (row["docName2"] != null && row["docName2"].ToString() != "")
                {
                    model.docName2 = row["docName2"].ToString();
                }
                if (row["docName3"] != null && row["docName3"].ToString() != "")
                {
                    model.docName3 = row["docName3"].ToString();
                }
                if (row["docName4"] != null && row["docName4"].ToString() != "")
                {
                    model.docName4 = row["docName4"].ToString();
                }
                if (row["docName5"] != null)
                {
                    model.docName5 = row["docName5"].ToString();
                }
                if (row["remark"] != null && row["remark"].ToString() != "")
                {
                    model.remark = row["remark"].ToString();
                }
                if (row["inputEmpNo"] != null && row["inputEmpNo"].ToString() != "")
                {
                    model.inputEmpNo = row["inputEmpNo"].ToString();
                }
                if (row["inputDate"] != null && row["inputDate"].ToString() != "")
                {
                    model.inputDate = DateTime.Parse(row["inputDate"].ToString());
                }
                if (row["nums"] != null && row["nums"].ToString() != "")
                {
                    model.nums = int.Parse(row["nums"].ToString());
                }
            }
            return model;
        }
        /// <summary>
        /// 下载DAL
        /// </summary>
        /// <param name="sheetID"></param>
        /// <returns></returns>
        public CustomsSystem.Model.document GetModel1(int sheetID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1  ");
            strSql.Append(" sheetID,docName1,docName2,docName3,docName4 ");
            strSql.Append(" from T_document ");
            strSql.Append(" where sheetID='" + sheetID + "'");
            CustomsSystem.Model.document model = new CustomsSystem.Model.document();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select docID,sheetID,docType,docName1,docName2,docName3,docName4,remark,inputEmpNo,inputDate,nums,username,CustomerName FROM  Cu_Customer,T_document a left join ");
            strSql.Append("(select username username,empnum from cu_user ) b on (a.inputempno=b.empnum) ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet GetList1(string strwhere,string kai ,string mo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select * from T_document,T_sheetInfo,Cu_User,Cu_Customer,info_documentMastert,Info_code   where T_document.sheetID=T_sheetInfo.sheetID and ");
            strSql.Append(" T_sheetInfo.cID=Cu_Customer.ID and T_document.inputEmpNo=Cu_User.EMPNum and T_document.doctype =info_documentMastert.docViewID  and Info_code.codeid=T_sheetInfo.goodstype and Info_code.codetype='goodtype'  ");
            if (strwhere.Trim() != "")
            {
                strSql.Append(" and " + strwhere);
            }
            if (kai.Trim() != "")
            {
                strSql.Append(" and t_document.inputDate>='" + kai + "'");
            }
            if (mo.Trim() != "")
            {
                strSql.Append(" and t_document.inputDate<='" + mo + "'");
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetList2(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select * from T_document,T_sheetInfo,Cu_User,Cu_Customer,info_documentMastert,Info_code   where T_document.sheetID=T_sheetInfo.sheetID and ");
            strSql.Append(" T_sheetInfo.cID=Cu_Customer.ID and T_document.inputEmpNo=Cu_User.EMPNum and T_document.doctype =info_documentMastert.docViewID  and Info_code.codeid=T_sheetInfo.goodstype and Info_code.codetype='goodtype'  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(CustomsSystem.Model.document model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_document set ");
            if (model.sheetID != null)
            {
                strSql.Append("sheetID=" + model.sheetID + ",");
            }
            if (model.docType != null)
            {
                strSql.Append("docType=" + model.docType + ",");
            }
            if (model.docName1 != null)
            {
                strSql.Append("docName1='" + model.docName1 + "',");
            }
            if (model.docName2 != null)
            {
                strSql.Append("docName2='" + model.docName2 + "',");
            }
            if (model.docName3 != null)
            {
                strSql.Append("docName3='" + model.docName3 + "',");
            }
            if (model.docName4 != null)
            {
                strSql.Append("docName4='" + model.docName4 + "',");
            }
            if (model.docName5 != null)
            {
                strSql.Append("docName5='" + model.docName5 + "',");
            }
            if (model.remark != null)
            {
                strSql.Append("remark='" + model.remark + "',");
            }
            if (model.inputEmpNo != null)
            {
                strSql.Append("inputEmpNo='" + model.inputEmpNo + "',");
            }
            if (model.nums != null)
            {
                strSql.Append("nums=" + model.nums + ",");
            }
            if (model.inputDate != null)
            {
                strSql.Append("inputDate='" + model.inputDate + "',");
            }

            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where sheetID=" + model.sheetID + " ");
            int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}

